import pandas as pd
import re
import glob
import datetime
import area
import construction

def RRU_type(x):
      result = re.findall(r"[RUA]{3}[0-9Em]{4,6}", x)
      if len(result) == 0:
           return x
      else:
           return result[0]

def generate():
    #读取RRU表和存量表
    A = glob.glob('./input/NR*.csv')
    B = glob.glob('./input/存量*.csv')

    # if int(B[0].split('_')[-1].rstrip('.csv')) > int(B[1].split('_')[-1].rstrip('.csv')):
    #     df = pd.read_csv(B[0], encoding='ANSI', on_bad_lines='skip')
    #     df.to_csv(B[0], encoding='utf-8-sig')
    # else:
    #     df = pd.read_csv(B[1], encoding='ansi')
    #     df.to_csv(B[1], encoding='utf-8-sig')

    if int(A[0].split('_')[-1].rstrip('.csv')) > int(A[1].split('_')[-1].rstrip('.csv')):
        A1 = pd.read_csv(A[1], dtype="object")
        A2 = pd.read_csv(A[0], dtype="object")
    else:
        A1 = pd.read_csv(A[0], dtype="object")
        A2 = pd.read_csv(A[1], dtype="object")

    if int(B[0].split('_')[-2].rstrip('.csv')) > int(B[1].split('_')[-2].rstrip('.csv')):
        B1 = pd.read_csv(B[1], dtype="object")
        B2 = pd.read_csv(B[0], dtype="object")
    else:
        B1 = pd.read_csv(B[0], dtype="object")
        B2 = pd.read_csv(B[1], dtype="object")

    #基站名称那一列合并成基站名称-柜-框-槽
    A1["基站名称"] = A1["基站名称"] + "-" + A1["柜号"].astype(str) + "-" + A1["框号"].astype(str) + "-" + A1["槽号"].astype(str)
    A2["基站名称"] = A2["基站名称"] + "-" + A2["柜号"].astype(str) + "-" + A2["框号"].astype(str) + "-" + A2["槽号"].astype(str)
    B1["网元名称"] = B1["网元名称"] + "-" + B1["机柜号"].astype(str) + "-" + B1["机框号"].astype(str) + "-" + B1["槽位号"].astype(str)
    B2["网元名称"] = B2["网元名称"] + "-" + B2["机柜号"].astype(str) + "-" + B2["机框号"].astype(str) + "-" + B2["槽位号"].astype(str)

    #RRU表 分离出基站名称、RRU名称、RRU类型、小区双工模式 字段列
    A1 = A1.query("RRU类型=='MRRU'")[["基站名称", "RRU名称", "RRU类型", "射频单元工作制式", "可用状态", "小区双工模式"]]
    A2 = A2.query("RRU类型=='MRRU'")[["基站名称", "RRU名称", "RRU类型", "射频单元工作制式", "可用状态", "小区双工模式"]]

    #存量表 先筛选MRRU，再分离出网元名称、资产序列号、特殊信息、BOM编码
    B1 = B1.query("单板名称=='MRRU'")[["网元名称", "资产序列号", "特殊信息", "BOM编码"]]
    B2 = B2.query("单板名称=='MRRU'")[["网元名称", "资产序列号", "特殊信息", "BOM编码"]]

    #去XJ-
    A1["基站名称"] = A1["基站名称"].apply(lambda x:x.lstrip("XJ-"))
    A2["基站名称"] = A2["基站名称"].apply(lambda x: x.lstrip("XJ-"))
    B1["网元名称"] = B1["网元名称"].apply(lambda x: x.lstrip("XJ-"))
    B2["网元名称"] = B2["网元名称"].apply(lambda x: x.lstrip("XJ-"))

    A1["RRU名称"] = A1["RRU名称"].astype(str).apply(lambda x: x.lstrip("XJ-"))
    A2["RRU名称"] = A2["RRU名称"].astype(str).apply(lambda x: x.lstrip("XJ-"))

    #网元名称改成基站名称
    B1.rename(columns={"网元名称" : "基站名称"}, inplace=True)
    B2.rename(columns={"网元名称" : "基站名称"}, inplace=True)

    #RRU表和存量表合并
    Result1 = pd.merge(A1, B1, "left")
    Result2 = pd.merge(A2, B2, "left")

    #生成新增RRU清单
    Result = list(set(list(Result2["资产序列号"])).difference(set(list(Result1["资产序列号"]))))
    Result = pd.DataFrame(Result, columns=["资产序列号"])
    Result = pd.merge(Result2, Result)
    project = pd.read_csv('./static/project.csv')
    Result = pd.merge(Result, project)
    Result["特殊信息"] = Result["特殊信息"].astype(str).apply(lambda x: RRU_type(x))    #提取设备型号
    # Result.insert(0, "序号", range(1, len(Result)+1))   #在第一列设置索引
    Result.to_csv("./output/Result.csv", index=False, encoding="utf_8_sig")

    #筛选今日开通RRU、分类、插入21NR表
    add = Result.query("是否在立项清单=='在立项清单'")[['RRU名称','特殊信息','资产序列号']]
    add.insert(0, "开通日期", f"{datetime.date.today().strftime('%Y-%m-%d %X')}")
    add.insert(0, "所属区分", '')
    add.insert(0, "建设单位", '')

    for i in range(len(add)):
        add['所属区分'][i] = area.get_area(add['RRU名称'][i])

    for i in range(len(add)):
        add['建设单位'][i] = construction.get_cu(add['所属区分'][i])

    NR21 = pd.read_excel('./output/21NR.xlsx')
    NR21 = pd.concat([NR21, add], ignore_index=True)
    NR21.to_excel('./output/21NR.xlsx', index=False)
    NR21.to_csv('./output/21NR.csv', encoding='utf-8-sig', index=False)